<?php
/**
 * User: chamlinsun@qq.com
 * Date: 17-11-20
 * Time: 下午3:33
 */
defined('SYSPATH') or die('No direct script access.');

class Controller_recievenwarehouse extends Controller_Admin{

    public function before(){
        try{
            ACL::Required('receipt_unwarehouse');
        }catch(HTTP_Exception_403 $e){
            Message::error('sorry，你没有权限进这个目录');
            $this->request->redirect(URL::base(TRUE));
        }
        parent::before();
    }
    /**
     * 根据SKU等查询条件查找出预约单号
     */
    public function Action_index(){
        $this->title = '已收货未入库报表';
        $view = View::factory('receipt/unwarehouse');

        $page = Pagination::factory(array(
            'items_per_page' => 100,
            'uri' => $this->request->uri(),
        ));

        if($_GET){
            $show = $this->getData($_GET,$page);
            if(isset($show['code'])){
                $view->set('error_msg',$show);
            }else{
                $view->set('show',$show);
                $view->set('page',$page);
            }
        }

/*         $stock = Purchase::getOutLandStock();
        $recieve_men = DB::select_array(array('id','nick'))
            ->from('users')
            ->execute('default')
            ->as_array('id','nick');
        $view->set('stock',$stock);
        $view->set('recieve_men',$recieve_men); */
        
        $view->set('get',$_GET);
        $this->response->body($view);
    }

    /**
     * 报表下载
     */
    public function Action_download(){
        $data = $this->getData($_GET);

        if(is_array($data)){
            $stores = Kohana::$config->load('vmi_warehouse')->as_array();
            foreach ($data as $k=>$v){
                $data[$k]['store'] = isset($stores[$v['warehouseid']])
                    ?$stores[$v['warehouseid']] : '未知仓库';
                $data[$k]['recieve_man'] = Erpuser::nick_by_id($v['recieve_man']);
                $data[$k]['status'] = '已经收货未质检';
            }
        }
        $resultNames = array(
            "purchaseorder_id" => "VMI预约单号",
            "id" => "收货单号",
            "detail_id" => "收货明细单号",
            "recieve_time" => "收货时间",
            "sku" => "SKU",
            "sku_name" => "中文名称",
            "purchase_quantity" => "采购数",
            "arrival_quantity" => "收货数",
            "check_count" => "质检数",
            "store" => "所属仓库",
            "recieve_man" => "收货人",
            "status"=>"状态",
        );
        $content = $this->createXLSTable($resultNames,$data);
        header("Content-type:application/vnd.ms-execl;charset=gb2312");
        header("Content-Disposition:attactment;filename=已收货未入库报表.xls");
        header("Pragma: no-cache");
        header("Expires: 0");
        echo $content;
        exit;
    }

    /**
     * 根据条件查询数据工具方法
     */
    protected function getData($get,$page = NULL){
        $error_msg = array();
        $where = '';

        $sql = "SELECT MAX( ri.`purchaseorder_id` )  as max, MIN( `purchaseorder_id` ) as min
            FROM `recieve_invoices` ri
            WHERE 1 ";

        $date_from = isset($get['date_from'])?$get['date_from']:null;
        $date_to = isset($get['date_to'])?$get['date_to']:null;
        $date_from && $where .= " AND ri.`recieve_time` >'".$date_from."'";
        $date_to && $where .= " AND ri.`recieve_time` <'".$date_to." 23:59:59'";
        $where .= " ORDER BY ri.`recieve_time` DESC";
        $sql .= $where;

        $date = DB::query(Database::SELECT,$sql)
            ->execute('newerp')
            ->current();
        //如果存在收货
        if($date['max']){
            $sql = "SELECT `purchaseorder_id`
                FROM `purchaseorder_details`
                WHERE `purchaseorder_id` >=".$date['min']."
                AND `purchaseorder_id` <= ".$date['max']."
                AND `recieve_quantity` > `check_quantity`";
            //存在可能收货未质检的采购单ID
            $notCheckPuIdArr = DB::query(Database::SELECT,$sql)
                ->execute('newerp')
                ->as_array(NULL,'purchaseorder_id');

            //找到收货未质检的收获明细ID
            if($notCheckPuIdArr){
                $notCheckPuIdArr = array_unique($notCheckPuIdArr);

                $sql = "SELECT rd.`id`
                    FROM `recieve_invoices` ri JOIN recieve_details rd   ON (ri.`id` = rd.`recieve_invoice_id`)
                    WHERE ri.`purchaseorder_id` IN (".join(',',$notCheckPuIdArr).")";

                //含有未质检的收获明细ID
                $notCheckReIDArr = DB::query(Database::SELECT,$sql)
                    ->execute('newerp')
                    ->as_array(NULL,'id');

                //根据这些采购单找到已经质检的收货明细ID
                $sql = "SELECT `recieve_detail_id`
                    FROM `check_quality_details`
                    WHERE `purchaseorder_id` IN (".join(',',$notCheckPuIdArr).")";

                $checkedReIdArr = DB::query(Database::SELECT,$sql)
                    ->execute('newerp')
                    ->as_array(NULL,'recieve_detail_id');
                $realNotCheckedIdArr = array_diff($notCheckReIDArr,$checkedReIdArr);

                if($realNotCheckedIdArr){
                    //根据收获明细单号查找信息
                    $sql = "SELECT ri.`id`,rd.`id` as detail_id,ri.`recieve_time`,
                        ri.`purchaseorder_id`, ri.`recieve_man`, `sku`, `sku_name`,
                        `purchase_quantity`, `arrival_quantity`, rd.`check_count`, ri.`warehouseid`
                        FROM `recieve_details` rd
                        JOIN `recieve_invoices` ri ON ( ri.`id` = rd.`recieve_invoice_id` )
                        WHERE rd.`id` IN ( ".join(',',$realNotCheckedIdArr)." )".$where;

                    if($page){
                        $pageObj = DB::query(Database::SELECT,$sql)
                            ->execute('newerp');
                        $page->total_items = $pageObj->count();
                        $limit = " LIMIT ".($page->current_page-1)*$page->items_per_page.",".$page->items_per_page;
                    }

                    $sql .= isset($limit)?$limit:'';
                    $show = DB::query(Database::SELECT,$sql)
                        ->execute('newerp')
                        ->as_array();
                    
                    return $show;
                }else{
                    $error_msg['code'] = 0;
                    $error_msg['msg']  = '当前所有收货都已质检入库';
                }
            }else{
                $error_msg['code'] = 0;
                $error_msg['msg']  = '不存在没收货的预约单';
            }
        }else{
            $error_msg['code'] = 0;
            $error_msg['msg']  = '该段时间内找不到收货记录';
        }
        
        return $error_msg;
    }
    /**
     *根据列明和已获得的相关信息数组生成table类型的字符串输出，用于后面进行excel表格生成
     */
    static protected function createXLSTable($columnNames,$datas){
        $keys = array_keys($columnNames);
        $content = "";
        $content .= "<table border='1'><tr>";
        foreach($columnNames as $key => $columnName){
            $content .= "<td>$columnName</td>";
        }
        $content .= "</tr>";
        foreach($datas as $key => $data){
            $content .= "<tr>";
            foreach($keys as $key){
                $content .= "<td>".$data[$key]."</td>";
            }
            $content .= "</tr>";
        }
        $content .= "</table>";
        return $content;
    }
}